
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, 2012 and Azure
-- --------------------------------------------------
-- Date Created: 04/09/2014 17:58:01
-- Generated from EDMX file: C:\Users\Artur\Documents\Visual Studio 2013\Projects\EntityFramework\FreddiesMarathonChallenge\DBModel\DBModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [FreddiesMarathonChallenge];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_CurriculumVitaeSkill]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Skills] DROP CONSTRAINT [FK_CurriculumVitaeSkill];
GO
IF OBJECT_ID(N'[dbo].[FK_CurriculumVitaeEmployment]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Employments] DROP CONSTRAINT [FK_CurriculumVitaeEmployment];
GO
IF OBJECT_ID(N'[dbo].[FK_CurriculumVitaeOtherQualification]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[OtherQualifications] DROP CONSTRAINT [FK_CurriculumVitaeOtherQualification];
GO
IF OBJECT_ID(N'[dbo].[FK_CurriculumVitaeLanguage]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Languages] DROP CONSTRAINT [FK_CurriculumVitaeLanguage];
GO
IF OBJECT_ID(N'[dbo].[FK_CurriculumVitaeEducation]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Educations] DROP CONSTRAINT [FK_CurriculumVitaeEducation];
GO
IF OBJECT_ID(N'[dbo].[FK_PublisherWorkAdvertisement]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[WorkAdvertisements] DROP CONSTRAINT [FK_PublisherWorkAdvertisement];
GO
IF OBJECT_ID(N'[dbo].[FK_ApplicantCurriculumVitae]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[CurriculumVitaes] DROP CONSTRAINT [FK_ApplicantCurriculumVitae];
GO
IF OBJECT_ID(N'[dbo].[FK_ApplicantWorkApplication]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[WorkApplications] DROP CONSTRAINT [FK_ApplicantWorkApplication];
GO
IF OBJECT_ID(N'[dbo].[FK_WorkAdvertisementWorkApplication]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[WorkApplications] DROP CONSTRAINT [FK_WorkAdvertisementWorkApplication];
GO
IF OBJECT_ID(N'[dbo].[FK_UserWithAddress_inherits_User]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Users_UserWithAddress] DROP CONSTRAINT [FK_UserWithAddress_inherits_User];
GO
IF OBJECT_ID(N'[dbo].[FK_Publisher_inherits_UserWithAddress]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Users_Publisher] DROP CONSTRAINT [FK_Publisher_inherits_UserWithAddress];
GO
IF OBJECT_ID(N'[dbo].[FK_Applicant_inherits_UserWithAddress]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Users_Applicant] DROP CONSTRAINT [FK_Applicant_inherits_UserWithAddress];
GO
IF OBJECT_ID(N'[dbo].[FK_SuperAdmin_inherits_User]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Users_SuperAdmin] DROP CONSTRAINT [FK_SuperAdmin_inherits_User];
GO
IF OBJECT_ID(N'[dbo].[FK_HeadHunter_inherits_UserWithAddress]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Users_HeadHunter] DROP CONSTRAINT [FK_HeadHunter_inherits_UserWithAddress];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Users]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Users];
GO
IF OBJECT_ID(N'[dbo].[CurriculumVitaes]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CurriculumVitaes];
GO
IF OBJECT_ID(N'[dbo].[Educations]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Educations];
GO
IF OBJECT_ID(N'[dbo].[Employments]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Employments];
GO
IF OBJECT_ID(N'[dbo].[Languages]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Languages];
GO
IF OBJECT_ID(N'[dbo].[OtherQualifications]', 'U') IS NOT NULL
    DROP TABLE [dbo].[OtherQualifications];
GO
IF OBJECT_ID(N'[dbo].[Skills]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Skills];
GO
IF OBJECT_ID(N'[dbo].[WorkAdvertisements]', 'U') IS NOT NULL
    DROP TABLE [dbo].[WorkAdvertisements];
GO
IF OBJECT_ID(N'[dbo].[WorkApplications]', 'U') IS NOT NULL
    DROP TABLE [dbo].[WorkApplications];
GO
IF OBJECT_ID(N'[dbo].[Users_UserWithAddress]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Users_UserWithAddress];
GO
IF OBJECT_ID(N'[dbo].[Users_Publisher]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Users_Publisher];
GO
IF OBJECT_ID(N'[dbo].[Users_Applicant]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Users_Applicant];
GO
IF OBJECT_ID(N'[dbo].[Users_SuperAdmin]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Users_SuperAdmin];
GO
IF OBJECT_ID(N'[dbo].[Users_HeadHunter]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Users_HeadHunter];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Users'
CREATE TABLE [dbo].[Users] (
    [UserId] int IDENTITY(1,1) NOT NULL,
    [UserName] nvarchar(max)  NOT NULL,
    [Password] nvarchar(max)  NOT NULL,
    [IsDeleted] bit  NOT NULL
);
GO

-- Creating table 'CurriculumVitaes'
CREATE TABLE [dbo].[CurriculumVitaes] (
    [CVId] int IDENTITY(1,1) NOT NULL,
    [Presentation] nvarchar(max)  NULL,
    [IsDeleted] bit  NOT NULL,
    [ApplicantUserId] int  NOT NULL
);
GO

-- Creating table 'Educations'
CREATE TABLE [dbo].[Educations] (
    [EducationId] int IDENTITY(1,1) NOT NULL,
    [EducationName] nvarchar(max)  NOT NULL,
    [SiteOfEducation] nvarchar(max)  NOT NULL,
    [StartDate] datetime  NOT NULL,
    [EndDate] datetime  NOT NULL,
    [EducationDescription] nvarchar(max)  NOT NULL,
    [Degree] int  NOT NULL,
    [IsDeleted] bit  NOT NULL,
    [CurriculumVitaeCVId] int  NOT NULL
);
GO

-- Creating table 'Employments'
CREATE TABLE [dbo].[Employments] (
    [EmploymentId] int IDENTITY(1,1) NOT NULL,
    [CompanyName] nvarchar(max)  NOT NULL,
    [Titel] nvarchar(max)  NOT NULL,
    [StartDate] datetime  NOT NULL,
    [EndDate] datetime  NOT NULL,
    [WorkDescription] nvarchar(max)  NOT NULL,
    [IsDeleted] bit  NOT NULL,
    [CurriculumVitaeCVId] int  NOT NULL
);
GO

-- Creating table 'Languages'
CREATE TABLE [dbo].[Languages] (
    [LanguageId] int IDENTITY(1,1) NOT NULL,
    [LanguageName] nvarchar(max)  NOT NULL,
    [LanguageGrade] int  NOT NULL,
    [LanguageYearsOfExperience] int  NOT NULL,
    [IsDeleted] bit  NOT NULL,
    [CurriculumVitaeCVId] int  NOT NULL
);
GO

-- Creating table 'OtherQualifications'
CREATE TABLE [dbo].[OtherQualifications] (
    [QualificationId] int IDENTITY(1,1) NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Organizer] nvarchar(max)  NOT NULL,
    [StartDate] datetime  NOT NULL,
    [EndDate] datetime  NOT NULL,
    [OnGoing] bit  NOT NULL,
    [QualificationDescription] nvarchar(max)  NOT NULL,
    [IsDeleted] bit  NOT NULL,
    [CurriculumVitaeCVId] int  NOT NULL
);
GO

-- Creating table 'Skills'
CREATE TABLE [dbo].[Skills] (
    [Skill_Id] int IDENTITY(1,1) NOT NULL,
    [SkillName] nvarchar(max)  NOT NULL,
    [SkillGrade] int  NOT NULL,
    [SkillDescription] nvarchar(max)  NOT NULL,
    [SkillYearsOfExperience] int  NOT NULL,
    [IsDeleted] bit  NOT NULL,
    [CurriculumVitaeCVId] int  NOT NULL
);
GO

-- Creating table 'WorkAdvertisements'
CREATE TABLE [dbo].[WorkAdvertisements] (
    [WorkAdvertisementId] int IDENTITY(1,1) NOT NULL,
    [PublishedDate] datetime  NOT NULL,
    [WorkTitle] nvarchar(max)  NOT NULL,
    [WorkLocation] nvarchar(max)  NOT NULL,
    [LastApplicationDate] datetime  NOT NULL,
    [WorkDescription] nvarchar(max)  NOT NULL,
    [EmploymentDurationInDays] nvarchar(max)  NOT NULL,
    [SalaryType] nvarchar(max)  NOT NULL,
    [ContactDescription] nvarchar(max)  NOT NULL,
    [IsDeleted] bit  NOT NULL,
    [PublisherUserId] int  NOT NULL
);
GO

-- Creating table 'WorkApplications'
CREATE TABLE [dbo].[WorkApplications] (
    [WorkApplicationId] int IDENTITY(1,1) NOT NULL,
    [ApplicantUserId] int  NOT NULL,
    [WorkAdvertisementWorkAdvertisementId] int  NOT NULL
);
GO

-- Creating table 'Users_UserWithAddress'
CREATE TABLE [dbo].[Users_UserWithAddress] (
    [Street] nvarchar(max)  NOT NULL,
    [PostalCode] nvarchar(max)  NOT NULL,
    [City] nvarchar(max)  NOT NULL,
    [Country] nvarchar(max)  NOT NULL,
    [Email] nvarchar(max)  NOT NULL,
    [Phone] nvarchar(max)  NOT NULL,
    [UserId] int  NOT NULL
);
GO

-- Creating table 'Users_Publisher'
CREATE TABLE [dbo].[Users_Publisher] (
    [EmployerName] nvarchar(max)  NOT NULL,
    [UserId] int  NOT NULL
);
GO

-- Creating table 'Users_Applicant'
CREATE TABLE [dbo].[Users_Applicant] (
    [FirstName] nvarchar(max)  NOT NULL,
    [LastName] nvarchar(max)  NOT NULL,
    [SocialSecurityNumber] nvarchar(max)  NOT NULL,
    [UserId] int  NOT NULL
);
GO

-- Creating table 'Users_SuperAdmin'
CREATE TABLE [dbo].[Users_SuperAdmin] (
    [UserId] int  NOT NULL
);
GO

-- Creating table 'Users_HeadHunter'
CREATE TABLE [dbo].[Users_HeadHunter] (
    [HeadHunterName] nvarchar(max)  NOT NULL,
    [UserId] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [UserId] in table 'Users'
ALTER TABLE [dbo].[Users]
ADD CONSTRAINT [PK_Users]
    PRIMARY KEY CLUSTERED ([UserId] ASC);
GO

-- Creating primary key on [CVId] in table 'CurriculumVitaes'
ALTER TABLE [dbo].[CurriculumVitaes]
ADD CONSTRAINT [PK_CurriculumVitaes]
    PRIMARY KEY CLUSTERED ([CVId] ASC);
GO

-- Creating primary key on [EducationId] in table 'Educations'
ALTER TABLE [dbo].[Educations]
ADD CONSTRAINT [PK_Educations]
    PRIMARY KEY CLUSTERED ([EducationId] ASC);
GO

-- Creating primary key on [EmploymentId] in table 'Employments'
ALTER TABLE [dbo].[Employments]
ADD CONSTRAINT [PK_Employments]
    PRIMARY KEY CLUSTERED ([EmploymentId] ASC);
GO

-- Creating primary key on [LanguageId] in table 'Languages'
ALTER TABLE [dbo].[Languages]
ADD CONSTRAINT [PK_Languages]
    PRIMARY KEY CLUSTERED ([LanguageId] ASC);
GO

-- Creating primary key on [QualificationId] in table 'OtherQualifications'
ALTER TABLE [dbo].[OtherQualifications]
ADD CONSTRAINT [PK_OtherQualifications]
    PRIMARY KEY CLUSTERED ([QualificationId] ASC);
GO

-- Creating primary key on [Skill_Id] in table 'Skills'
ALTER TABLE [dbo].[Skills]
ADD CONSTRAINT [PK_Skills]
    PRIMARY KEY CLUSTERED ([Skill_Id] ASC);
GO

-- Creating primary key on [WorkAdvertisementId] in table 'WorkAdvertisements'
ALTER TABLE [dbo].[WorkAdvertisements]
ADD CONSTRAINT [PK_WorkAdvertisements]
    PRIMARY KEY CLUSTERED ([WorkAdvertisementId] ASC);
GO

-- Creating primary key on [WorkApplicationId] in table 'WorkApplications'
ALTER TABLE [dbo].[WorkApplications]
ADD CONSTRAINT [PK_WorkApplications]
    PRIMARY KEY CLUSTERED ([WorkApplicationId] ASC);
GO

-- Creating primary key on [UserId] in table 'Users_UserWithAddress'
ALTER TABLE [dbo].[Users_UserWithAddress]
ADD CONSTRAINT [PK_Users_UserWithAddress]
    PRIMARY KEY CLUSTERED ([UserId] ASC);
GO

-- Creating primary key on [UserId] in table 'Users_Publisher'
ALTER TABLE [dbo].[Users_Publisher]
ADD CONSTRAINT [PK_Users_Publisher]
    PRIMARY KEY CLUSTERED ([UserId] ASC);
GO

-- Creating primary key on [UserId] in table 'Users_Applicant'
ALTER TABLE [dbo].[Users_Applicant]
ADD CONSTRAINT [PK_Users_Applicant]
    PRIMARY KEY CLUSTERED ([UserId] ASC);
GO

-- Creating primary key on [UserId] in table 'Users_SuperAdmin'
ALTER TABLE [dbo].[Users_SuperAdmin]
ADD CONSTRAINT [PK_Users_SuperAdmin]
    PRIMARY KEY CLUSTERED ([UserId] ASC);
GO

-- Creating primary key on [UserId] in table 'Users_HeadHunter'
ALTER TABLE [dbo].[Users_HeadHunter]
ADD CONSTRAINT [PK_Users_HeadHunter]
    PRIMARY KEY CLUSTERED ([UserId] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [CurriculumVitaeCVId] in table 'Skills'
ALTER TABLE [dbo].[Skills]
ADD CONSTRAINT [FK_CurriculumVitaeSkill]
    FOREIGN KEY ([CurriculumVitaeCVId])
    REFERENCES [dbo].[CurriculumVitaes]
        ([CVId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CurriculumVitaeSkill'
CREATE INDEX [IX_FK_CurriculumVitaeSkill]
ON [dbo].[Skills]
    ([CurriculumVitaeCVId]);
GO

-- Creating foreign key on [CurriculumVitaeCVId] in table 'Employments'
ALTER TABLE [dbo].[Employments]
ADD CONSTRAINT [FK_CurriculumVitaeEmployment]
    FOREIGN KEY ([CurriculumVitaeCVId])
    REFERENCES [dbo].[CurriculumVitaes]
        ([CVId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CurriculumVitaeEmployment'
CREATE INDEX [IX_FK_CurriculumVitaeEmployment]
ON [dbo].[Employments]
    ([CurriculumVitaeCVId]);
GO

-- Creating foreign key on [CurriculumVitaeCVId] in table 'OtherQualifications'
ALTER TABLE [dbo].[OtherQualifications]
ADD CONSTRAINT [FK_CurriculumVitaeOtherQualification]
    FOREIGN KEY ([CurriculumVitaeCVId])
    REFERENCES [dbo].[CurriculumVitaes]
        ([CVId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CurriculumVitaeOtherQualification'
CREATE INDEX [IX_FK_CurriculumVitaeOtherQualification]
ON [dbo].[OtherQualifications]
    ([CurriculumVitaeCVId]);
GO

-- Creating foreign key on [CurriculumVitaeCVId] in table 'Languages'
ALTER TABLE [dbo].[Languages]
ADD CONSTRAINT [FK_CurriculumVitaeLanguage]
    FOREIGN KEY ([CurriculumVitaeCVId])
    REFERENCES [dbo].[CurriculumVitaes]
        ([CVId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CurriculumVitaeLanguage'
CREATE INDEX [IX_FK_CurriculumVitaeLanguage]
ON [dbo].[Languages]
    ([CurriculumVitaeCVId]);
GO

-- Creating foreign key on [CurriculumVitaeCVId] in table 'Educations'
ALTER TABLE [dbo].[Educations]
ADD CONSTRAINT [FK_CurriculumVitaeEducation]
    FOREIGN KEY ([CurriculumVitaeCVId])
    REFERENCES [dbo].[CurriculumVitaes]
        ([CVId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CurriculumVitaeEducation'
CREATE INDEX [IX_FK_CurriculumVitaeEducation]
ON [dbo].[Educations]
    ([CurriculumVitaeCVId]);
GO

-- Creating foreign key on [PublisherUserId] in table 'WorkAdvertisements'
ALTER TABLE [dbo].[WorkAdvertisements]
ADD CONSTRAINT [FK_PublisherWorkAdvertisement]
    FOREIGN KEY ([PublisherUserId])
    REFERENCES [dbo].[Users_Publisher]
        ([UserId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PublisherWorkAdvertisement'
CREATE INDEX [IX_FK_PublisherWorkAdvertisement]
ON [dbo].[WorkAdvertisements]
    ([PublisherUserId]);
GO

-- Creating foreign key on [ApplicantUserId] in table 'CurriculumVitaes'
ALTER TABLE [dbo].[CurriculumVitaes]
ADD CONSTRAINT [FK_ApplicantCurriculumVitae]
    FOREIGN KEY ([ApplicantUserId])
    REFERENCES [dbo].[Users_Applicant]
        ([UserId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ApplicantCurriculumVitae'
CREATE INDEX [IX_FK_ApplicantCurriculumVitae]
ON [dbo].[CurriculumVitaes]
    ([ApplicantUserId]);
GO

-- Creating foreign key on [ApplicantUserId] in table 'WorkApplications'
ALTER TABLE [dbo].[WorkApplications]
ADD CONSTRAINT [FK_ApplicantWorkApplication]
    FOREIGN KEY ([ApplicantUserId])
    REFERENCES [dbo].[Users_Applicant]
        ([UserId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ApplicantWorkApplication'
CREATE INDEX [IX_FK_ApplicantWorkApplication]
ON [dbo].[WorkApplications]
    ([ApplicantUserId]);
GO

-- Creating foreign key on [WorkAdvertisementWorkAdvertisementId] in table 'WorkApplications'
ALTER TABLE [dbo].[WorkApplications]
ADD CONSTRAINT [FK_WorkAdvertisementWorkApplication]
    FOREIGN KEY ([WorkAdvertisementWorkAdvertisementId])
    REFERENCES [dbo].[WorkAdvertisements]
        ([WorkAdvertisementId])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_WorkAdvertisementWorkApplication'
CREATE INDEX [IX_FK_WorkAdvertisementWorkApplication]
ON [dbo].[WorkApplications]
    ([WorkAdvertisementWorkAdvertisementId]);
GO

-- Creating foreign key on [UserId] in table 'Users_UserWithAddress'
ALTER TABLE [dbo].[Users_UserWithAddress]
ADD CONSTRAINT [FK_UserWithAddress_inherits_User]
    FOREIGN KEY ([UserId])
    REFERENCES [dbo].[Users]
        ([UserId])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- Creating foreign key on [UserId] in table 'Users_Publisher'
ALTER TABLE [dbo].[Users_Publisher]
ADD CONSTRAINT [FK_Publisher_inherits_UserWithAddress]
    FOREIGN KEY ([UserId])
    REFERENCES [dbo].[Users_UserWithAddress]
        ([UserId])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- Creating foreign key on [UserId] in table 'Users_Applicant'
ALTER TABLE [dbo].[Users_Applicant]
ADD CONSTRAINT [FK_Applicant_inherits_UserWithAddress]
    FOREIGN KEY ([UserId])
    REFERENCES [dbo].[Users_UserWithAddress]
        ([UserId])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- Creating foreign key on [UserId] in table 'Users_SuperAdmin'
ALTER TABLE [dbo].[Users_SuperAdmin]
ADD CONSTRAINT [FK_SuperAdmin_inherits_User]
    FOREIGN KEY ([UserId])
    REFERENCES [dbo].[Users]
        ([UserId])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- Creating foreign key on [UserId] in table 'Users_HeadHunter'
ALTER TABLE [dbo].[Users_HeadHunter]
ADD CONSTRAINT [FK_HeadHunter_inherits_UserWithAddress]
    FOREIGN KEY ([UserId])
    REFERENCES [dbo].[Users_UserWithAddress]
        ([UserId])
    ON DELETE CASCADE ON UPDATE NO ACTION;
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------